Méthodologie

Dans cette partie, nous construirons les clés temps et par la même occasion les coûts intermédiaires ventilés grâce à ces clés temps. Nous partons de la matrice de paramétrage qui a été importée ci-haut. Le fichier de paramétrage contient les éléments suivants :

• Les 2 premières colonnes décrivent les codes et les libellés pour lesquels on doit calculer les clés temps. Les codes en ligne contiennent à la fois des activités opérationnelles COD, mais aussi les autres activités qui doivent être ventilées avec les clés temps.
• la 3 ème colonne décrit les temps agent alloué à l’activité
• les autres colonnes décrivent COI et les CSS qui doivent être ventilées sur les activités en lignes. Ainsi si pour une colonne on met ‘o’ pour une activité cela signifie que les charges de cette activité doivent être ventilées sur l’activité en ligne correspondante.

Cette façon de faire, suppose donc que nous devons connaître en amont les charges des différentes activités qui sont en colonne. Cette information se trouve dans la base de charge avec la colonne Ch_AP_Ret qui représente les charges après retraitement. Mais nous la calculerons dans la suite à partir des données directes issues de la base de charge Essayons de formaliser mathématiquement le calcul des clés temps.

• Soit \(K=(1,...,k...,K)\) l’ensemble des activités sur lesquels on doit calculer des clés temps
• Soit \(J=(1,...,j...,J)\) l’ensemble des activités pour lesquels on doit ventiler des charges.
• Soit \(T_{k}\) le temps alloué à l’opération \(k\)
• Soit \(\mathbb{1}_{k}(o)\) la fonction indicatrice qui indique si une activité de l’ensemble \(J\) doit être ventilé sur l’activité \(k\) de l’ensemble \(K\). Cette fonction représente le fichier de paramétrage avec les options “o”
• Soit \(W_{k}^{j}\) la clé temps de l’activité \(j\) à ventiler sur l’activité \(k\)

\[ W_{k}^{j}=\frac{T_{k}*\mathbb{1}_{k}(o) }{\sum_{i \in (\mathbb{1}_{k}(o)} \mathbb{1}_{i}(o)*T_{i} }\] Traduit litéralement, cette formule signifie que le poids d’une activité est égal au rapport de son temps sur la somme des temps des activité sur laquelle la charge doit être ventilée.

Ce poids est appliqué à la charge après traitement des opérations à ventiler. Soit \(Charge_{k}^{j}\) la charge de l’opération \(j\) à ventiler sur l’opération \(k\), nous aurons donc : \[ChargeTA_{k}^{j}=Charge_{k}^{j}*W_{k}^{j}\]

# Jointure de la table des données avec la table de marquage et création de la variable de code
data<-data %>%mutate(#code=stri_replace_all_fixed(paste('code_',Code_manuel), " ", ""),
                     Ch_AP_Ret=ifelse(is.na(Ch_AP_Ret),0,Ch_AP_Ret))%>%
  filter(is.na(Code_manuel)==0)

# Calcul des charges intermédiaires pour la construction des taux de frais
#Cette table sera utilisée dans la suite pour le calcul des clés au taux de frais
Charges_for_TA<-data%>% 
    group_by(code)%>%
     summarise(charges_TA=sum(Ch_AP_Ret))

a<-as.data.frame(t(Charges_for_TA))
a<-a[-1,]
colnames(a)<-t(Charges_for_TA[,1])
a<- data.frame(lapply(a, function(x) as.numeric(as.character(x))))


## Calcul des charges 
drop_var<-names(a) %in%CDO
a<-a[!drop_var]


#Stockage des variables concernées : ce sont les variables en colonne
liste_col=colnames(param_temps)[4:length(colnames(param_temps))]





cle_temps<-param_temps[,1:2]

#Boucle pour construire la variable de clé temps en fonction des effectifs temps
for (i in 1:length(liste_col))
{
  # Nouvelle variable de clé
  var<-stri_replace_all_fixed( paste("weight_",liste_col[i]), " ", "")
  #Variable sur laquelle calculer la clé
  code<-stri_replace_all_fixed(liste_col[i]," ","")
    sum_temps=sum(param_temps[which(param_temps[[paste(code)]]=="o"),]$temps_min)
  count_oui=nrow(param_temps[which(param_temps[[paste(code)]]=="o"),])
  # Construction de la variable
  cle_temps[[paste(var)]]=ifelse(param_temps[[paste(code)]]!="o",0,
                              param_temps$temps_min/sum(param_temps[which(param_temps[[paste(code)]]=="o"),]$temps_min))
#}
  # Traitement des cas particuliers où un seul poids existe, on fait le rapport sur la somme des effectifs
  cle_temps[[paste(var)]]<-ifelse(param_temps[[paste(code)]]=="o" 
                                    & param_temps$Code_Activite==11 
                                    & count_oui==1
                                ,(param_temps$temps_min/sum(param_temps$temps_min))
                                ,cle_temps[[paste(var)]])
 
}

Les traitements spécifiques

Toutes les activités ne suivent pas la même logique mathématique énoncée ci-haut pour calculer leurs clés temps. Il existe des cas spécifiques ; ces derniers sont :

• La charge 32: Directeur de Secteur (DS) est répartie via une clé temps agent des personnes encadrées. Cette clé est directement intégrée dans la table de paramétrage des clés temps en dur.
• La charge 33: Responsable Espace Comercial (REC) est répartie via une clé temps agent des personnes encadrées. Cette clé est directement intégrée dans la table de paramétrage des clés temps en dur.
• La charge 30 CIE - Loyers et Charges Immobilicres (hors Cplts de Loyers), pour cette charge le réseau donne un montant qui se déverse sur l’hébergement des facteurs, ainsi on doit calculer les clés de sorte à intégrer cette partie et avoir une répartition à 100%. La valeur fournie par le réseau est stockée en dur dans la variable charge_heberge_facteur
• La charge 41 SSM - Formation c’est le même pricipe que la charge 30 , sauf qu’on doit retirer de la charge les prestation de formation, ensuite allouer un montant à la formation LBC et répartir le reste de sorte à tenir 100% avec la charge total en dehors des prestations de formation.

## traitement spécifique
cle_temps$weight_code_32<-param_temps$code_32
cle_temps$weight_code_33<-param_temps$code_33

# traitement des clé mixtes

# Pour le code 30 on sait 
cle_temps<-cle_temps%>%
  mutate(weight_code_30=ifelse(is.na(weight_code_30),weight_code_30,weight_code_30*(a$code_30-charge_heberge_facteur)/a$code_30))

#sum(cle_temps$weight_code_30,na.rm=T)
cle_temps<-cle_temps%>%
  mutate(weight_code_30=ifelse(Code_Activite==2,1-sum(cle_temps$weight_code_30,na.rm=T),weight_code_30))
#sum(cle_temps$weight_code_30,na.rm=T)

# Pour le code 41
# on supprime les charges à enlever
a<-a%>%
  mutate(code_41=code_41-prestation_formation)

cle_temps<-cle_temps%>%
  mutate(weight_code_41=ifelse(is.na(weight_code_41),weight_code_41,weight_code_41*(a$code_41-Formation_LCB)/a$code_41))
#sum(cle_temps$weight_code_41,na.rm=T)

cle_temps<-cle_temps%>%
  mutate(weight_code_41=ifelse(Code_Activite==7,1-sum(cle_temps$weight_code_41,na.rm=T),weight_code_41))

#sum(cle_temps$weight_code_41,na.rm=T)

# Conversion des variables en numerique
#cle_temps<-cle_temps%>%mutate_each(funs(as.numeric), starts_with("weight_"))
cle_temps<-cle_temps%>%mutate_at(vars(starts_with("weight_")), as.numeric)

Calcul des charges réparties aux clés temps : Répartition primaire

Après avoir calculé les clés temps et traité les cas spécifiques, nous pouvons calculer les charges ventilés aux clés temps. Nous appliquons juste le même principe énoncé ci-haut en multipliant les charges par les clés obtenues. C’est une répartition sur les activités opérationnelles sans prendre en compte la répartition sur les activités non opérationnelles.

###################################################################################################################
#Boucle pour construire la variable des charges intermédiaires avec les clés temps en fonction des effectifs temps#
###################################################################################################################
for (i in 1:length(liste_col))
{
  # Nouvelle variable de clé
  var<-stri_replace_all_fixed( paste("weight_",liste_col[i]), " ", "")
  #Variable sur laquelle calculer la clé
  code<-stri_replace_all_fixed(liste_col[i]," ","")
  # Construction de la variable
  param_temps[[paste(var)]]=a[1,i]*cle_temps[[paste(var)]]
}

Ci-dessous, la matrice des clés temps

kable(cle_temps, "html",caption = "Matrice des clés temps") %>%
  kable_styling(bootstrap_options = "striped", full_width = F) %>%
  scroll_box(width = "1000px", height = "500px")

Ci-dessous la matrice des charges obtenus par ventilation avec les clés temps

Le programme ci-dessus nous a permis de calculer les clés temps, mais aussi les charges intermédiaires liées à ces clés temps.

Dans la partie qui suit, nous allons calculer les clés au taux de frais qui dépendent des charges intermédiaires qui ont été calculées à partir des temps.

Méthodologie

Les clefs basées sur les coûts (taux de frais) doivent être calculées à partir des coûts directs des activités opérationnelles (codes 1 à 25) + charges indirectes réparties (codes 26 à 35). Les coûts directs des activités opérationnelles ont été identifiés via le mécanisme de marquage. Nous allons coupler cette information avec les coûts indirectes issues de la ventilation via les clés temps pour déterminer les clés au taux de frais.

Ces clés au taux de frais s’appliquent à des activités opérationnelles. Pour définir les opérations sur lesquelles ils s’appliquent, nous utilisons un autre fichier de paramétrage similaire au paramétrage des clés temps.
Voici les étapes de calcul des clés au taux de frais sur les différentes activités qui doivent en bénéficier.

1. On calcule les coûts directes des opérations ayant les codes 1 à 25
2. On calcule les coûts indirectes issues de la répartition via les clés temps pour les codes 11 à 20
3. On somme les coûts directs avec les coûts indirects des opérations de code 1 à 25 pour les charges sur les opérations de 26 à 35.
4. On importe le paramétrage des taux de frais.
5. On calcule le poids.

Soit \(\mathbb{1}_{j}(o)\) l’indicatrice qui indique si une charge doit être déversée ou non sur les activités opérationnelles. On note \(CDI^{j}\) les charges directes et indirectes de l’activité opérationnelle \(j\).
On note \(Wtf_{k}^{j}\) la clé au taux de frais de l’activité k sur l’activité j. \(Wtf_{k}^ {j}\) est définis par :

\[ Wtf_{k}^{j}=\frac{CDI^{j}}{\sum_{i}(\mathbb{1}_{i}(o))*CDI^{i}} \]

## Création de la table des coûts directes des activités opérationnelles
cout_dir_op<-Charges_for_TA[which(Charges_for_TA$code %in% CDO),] #c('code_1','code_2','code_3','code_4','code_5','code_6','code_7','code_8','code_9','code_10')),]

# calcul des coûts indirectes
couts_indir<-param_temps%>%filter(Code_Activite%in% c(1:25))%>%
                            select(.dots = CIO_int)
names(couts_indir)<-CIO_int

# Calcul des coûts indirecte
couts_indir$code<-stri_replace_all_fixed( paste("code_",couts_indir$Code_Activite), " ", "")

# jointure des coûts directes avec les coûts indirectes
couts_dir_indir<-full_join(cout_dir_op,couts_indir)
couts_dir_indir<- arrange(couts_dir_indir, code)
drop.cols<-"Code_Activite"
couts_dir_indir<-couts_dir_indir%>%select(-one_of(drop.cols))

# Remplacement par zéros des coûts manquants
couts_dir_indir[is.na(couts_dir_indir)] <- 0
couts_dir_indir<-couts_dir_indir%>%
  mutate(charges=rowSums(couts_dir_indir[,2:12]))%>%
  select(code,charges)

  
## Utilisation du paramétrage des clés taux de frais
param_tf<-param_tf%>%
  mutate(code=stri_replace_all_fixed( paste("code_",Code_Activite), " ", ""))%>%
  filter(code %in% couts_indir$code)%>%
  arrange(code)

couts_dir_indir<-left_join(couts_dir_indir,param_tf)

rm("couts_indir", "cout_dir_op")

Après avoir arrangé les tables on peut procéder au calcul des clés au taux de frais.

liste_col=colnames(couts_dir_indir)[5:(length(colnames(couts_dir_indir)))]

cle_tf<-couts_dir_indir[,c(1,3,4)]
#Boucle pour construire la variable de clé temps en fonction des effectifs temps
for (i in 1:length(liste_col))
{
  # Nouvelle variable de clé
  var<-stri_replace_all_fixed( paste("weight_",liste_col[i]), " ", "")
  #Variable sur laquelle calculer la clé
  code<-stri_replace_all_fixed(liste_col[i]," ","")
  # Construction de la variable
  cle_tf[[paste(var)]]=ifelse(couts_dir_indir[[paste(code)]]!="o",0,
                              couts_dir_indir$charges/sum(couts_dir_indir[which(couts_dir_indir[[paste(code)]]=="o"),]$charges))
  
}

Traitement spécifique de la DPOM COrse

La clé taux de frais qui est calculé ci-haut pour la l’activité ST - Transverses en DPOM-DCR n’est pas bonne, cette clé doit être calculé dur la base de la répartition des charges après retraitement sur ls activités pour lesquelles elle doit se ventiler. Le programme ci-dessous va corriger cette clé.

#Calcule des charges directes sur la DPOM CORSE
dpom<-data%>%filter(Code_manuel %in% c(1,5,7,8,9,12),
                    Ste>6000 )%>%
             group_by(code)%>%
  summarise(charges_dpom=sum(Ch_AP_Ret))%>%
  mutate(cle_dpom=charges_dpom/sum(charges_dpom))

# Correction de la clé
cle_tf$weight_code_36=ifelse(cle_tf$code==dpom$code,dpom$cle_dpom,cle_tf$weight_code_36)            

Méthodologie

Nous avons vu avec les clés temps que certaines activités se déversent sur d’autres activités non-opérationnelles. Ansi certaines CIO sont répartis sur d’autres CIO qui sont eux-mêmes répartis sur les CDO. Il convient donc de mettre à jour les clés sur les CIO des activités qui ont un bout qui est réparti sur les autres CIO.
Dans cette partie, nous allons nous atteler à cette tâche pour obtenir les clés finales sur les activités opérationnelles.

# On va créer une matrice de clé qui combine les clés au tausx de frais et les clés temps
cle_fin<-cle_temps

act_tf<-param_type_cle%>%
        filter(type_cle%in%c("taux de frais","temps et taux de frais"))%>%
        mutate(var=stri_replace_all_fixed( paste("weight_",Code), " ", ""))%>%
        select(var)

list_col<-act_tf$var

drop_var<-names(cle_fin) %in%list_col
cle_fin<-cle_fin[!drop_var]

# Découpage pour ordonner par code 
mystrsplit <- function(x, pattern, part=2){
  return(strsplit(x, pattern)[[1]][part])
}
# Vectorize it so that it can handle vector arguments of x
mystrsplit <- Vectorize(mystrsplit, vectorize.args = "x")

cle_tf<- cle_tf%>%mutate(Code_Activite=as.numeric(mystrsplit(code, '\\_', 2)))%>%
                  arrange(Code_Activite)



dat1<-cle_tf[,list_col]
dat2 <- data.frame(matrix(nrow = nrow(cle_fin)-nrow(dat1), ncol = ncol(dat1)))

names(dat2) <- names(dat1)

dat<-bind_rows(dat1, dat2)

cle_fin<-cbind(cle_fin,dat)

# Ordonner les colonnes
cle_fin<-cle_fin[,order(colnames(cle_fin))]

# Nettoyage de la mémoire
rm("dat1", "dat2", "dat","act_tf")

On peut commencer le calcul à proprement dit des clés finales. Mais avant de commencer, on va traiter des cas spécifiques. Il s’agit des clés avec les codes 50 et 52. Ces derniers se répartissent en parti grâce aux clés temps sur l’activité 11 puis le reste est réparti au taux de frais.

# on sait que pour les activités 50 et 52, une partie se répartie via le temps et le reste se réparti avec les clés taux de frais. Voici le traitement de cette règle:

cle_temps_heb=cle_temps[which(cle_temps$Code_Activite==11),]$weight_code_50
cle_fin<-cle_fin%>%
  mutate(weight_code_50=ifelse(is.na(weight_code_50),weight_code_50,weight_code_50*(1-cle_temps_heb)))

cle_fin[which(cle_fin$Code_Activite==11),]$weight_code_50=cle_temps_heb
#sum(cle_fin$weight_code_50,na.rm=T)

cle_fin<-cle_fin%>%
  mutate(weight_code_52=ifelse(is.na(weight_code_52),weight_code_52,weight_code_52*(1-cle_temps_heb)))

cle_fin[which(cle_fin$Code_Activite==11),]$weight_code_52=cle_temps_heb
#sum(cle_fin$weight_code_52,na.rm=T)

Une fois ces cas gérés, nous passons au traitement des charges qui se déversent sur d’autres charges non-opérationnelles. Nous en profitons pour créer en même temps la table des clés finales.
Avant de commencer ce traitement à proprement dit, essayons de formaliser concrétement ce que nous faisont dans cette partie.

• On note \(W^{ij}_{CDO-CIO}\) la clé primaire de la CIO \(j\) qui se déverse dans un CDO \(i\).
• On note \(W^{jk}_{CIO-CIO}\) la clé primaire de la CIO \(j\) qui se déverse dans un CIO \(k\).

La clé finale de l’activité j sur l’activité opérationnelle i est données par :

\[ W^{ij}_{fin}=W^{ij}_{CDO-CIO} + \sum_{k}W^{jk}_{CIO-CIO}*W^{ik}_{CDO-CIO} \]

Ce formalise suppose un ordre de traitement des clés pour tenir compte des cas d’imbrication. Dans le cas de la cascade en date de février 2018, seul l’activité 30 présente une imbrication de clés ainsi, il convient de traiter cette activité en premier et de mettre à jour les clés primaires avant le calcul de la clé finale des autres CIO.

# Selection des codes activité 1 à 25: les COD 
# C'est la table de la répartition sur les activités opérationnelles
# Ces clés ne font pas 100% car il y a une partie qui va se déverser sur les activités non opérationnelles
cle_ini<-cle_fin%>%
  filter(Code_Activite%in%c(1:25))%>%
  arrange(Code_Activite)

# On remplace les valeurs manquantes par zeros
cle_ini[is.na(cle_ini)]<-0

# On selectionne clés sur les COD des activités COI qui se déversent dans d'autres COI. Voir si on ne peut pas les mettre en paramètre.
# C'est la répartion sur les activités opérationnelles des activités non opérationnelles sur lesquelles se déversent d'autres activités non opérationnelles
cle_int<-cle_ini%>%
  select(c("weight_code_30",
            "weight_code_32",
            "weight_code_33",
            "weight_code_34",
            "weight_code_35",
            "weight_code_38",
            "weight_code_39",
            "weight_code_40",
            "weight_code_41",
            "weight_code_45"))
  
# transoposition de la matrice pour faciliter les opérations de calcul
cle_int<-as.data.frame(t(cle_int))

# Clé des COI qui se déversent sur les autres COI
cle_venti<-cle_temps%>%
  filter(Code_Activite%in%c(30,
                          32,
                          33,
                          34,
                          35,
                          38,
                          39,
                          40,
                          41,
                          45))%>%
  arrange(Code_Activite)%>%
  select(3:7)
  
cle_venti[is.na(cle_venti)]<-0  

# Append des deux tables 
cle_venti<-cbind(cle_venti,cle_int)


## Cette fonction permet de calculer la clé correcte des COD pour les COI qui se déversent sur d'autre COI
correct_cle <- function(data, cle){
var<-data[[paste(cle)]]
test<-data%>%
          mutate_at(vars(starts_with("V")), funs(.*var))
          #mutate_each(funs(.*var), starts_with("V")) vars(starts_with("weight_"))
#test<-test %>% summarize_each(funs(sum), starts_with("V"))  
test<-test %>% summarize_at(vars(starts_with("V")),funs(sum))  
test<-as.data.frame(t(test))


insertRow <- function(existingDF, newrow, r) {
  existingDF[seq(r+1,nrow(existingDF)+1),] <- existingDF[seq(r,nrow(existingDF)),]
  existingDF[r,] <- newrow
  existingDF
}

#newrow <- 0
#test <- insertRow(test,newrow,3)
test<-as.matrix(test)
#cle_venti$newvar<-test[,1]
return(test)
}

cle_cor<-cle_ini[,1:7]
#Modif
cle_cor$cor_weight_code_30=as.numeric(correct_cle(cle_venti,"weight_code_30")+cle_cor$weight_code_30)

cle_venti[1,6:length(cle_venti)]<-t(cle_cor$cor_weight_code_30)

liste_col=colnames(cle_venti)[1:4]

Calcul des clés finales

# Boucle pour le calcul des clés finales
for (i in 1:length(liste_col))
{
  # Nouvelle variable de clé
  var<-stri_replace_all_fixed( paste("cor_",liste_col[i]), " ", "")
  #Variable sur laquelle calculer la clé
  code<-stri_replace_all_fixed(liste_col[i]," ","")
  # Construction de la variable
  cle_cor[[paste(var)]]=as.numeric(correct_cle(cle_venti,code)+cle_cor[[paste(code)]])
}
#??? déplacement en dernier position
cle_cor<-cle_cor%>%select(-cor_weight_code_30,cor_weight_code_30)

col_names<-colnames(cle_cor[,3:7])
cle_cor<-cle_cor[,-c(3:7)]
names(cle_cor)[3:length(cle_cor)]<-col_names
cle_cor<-cbind(cle_cor,cle_ini[,8:ncol(cle_ini)])

rm("cle_ini", "cle_int", "cle_venti","a","cle_fin","Charges_for_TA","couts_dir_indir")

Vérification des clés

Application des clés à la base des charges

Dans les étapes précédentes, nous avons calculer les pondérations de ventilation de toutes les activités sur les charges opérationnelles.
Ces pondérations doivent être appliquée aux activités non opérationnelles pour avoir la cascade des coûts.

code<-stri_replace_all_fixed( paste("charge_code_",cle_cor$Code_Activite), " ", "")
cle_cor<-cbind(code,cle_cor)

# Transposition de la table des clés
cols <- as.character(cle_cor$code)
test<-cle_cor[,-c(1,2,3)]
rownames(test)<-cols

t_cle_cor<-as.data.frame(t(test))
code<-row.names(t_cle_cor)

t_cle_cor$code<-substr(code, 8, 15)

## Fusion avec la base de charge

data_cascade<-left_join(data,t_cle_cor)

# Calcul de la ventilation sur les activités opérationnelles
data_cascade<-data_cascade%>% mutate_at(vars(starts_with("charge_code_")), funs(.*Ch_AP_Ret))

rm(list=ls()[! ls() %in% c("data_cascade","cols","fldc","Part_CVAE","Part_CFE","file.name", "path.to.file")])

Lorsque la variable concerne une charge opérationnelle, on met le montant de la charge après retraitement

for (i in 1:length(cols))
{
  # Nouvelle variable de clé
  var<-cols[i]
  # Construction de la variable : Si le code est le même que la variable, on met la charge de la variable
  data_cascade[[paste(var)]]=ifelse(data_cascade$code== substring(cols[i],8),data_cascade$Ch_AP_Ret,data_cascade[[paste(var)]])
  
}

rm("cols","i","var")